package kr.hkit.powerjava.chapter26.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;

import kr.hkit.powerjava.chapter26.common.ConnectionFactory;
import kr.hkit.powerjava.chapter26.common.DBUtil;
import kr.hkit.powerjava.chapter26.dto.Department;

public class DepartmentDAO implements DaoInterface<Department> {
	
	private Connection connection;
	private PreparedStatement pstmt; //2번째꺼 java.sql
	
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	
	@Override
	public void insertItem(Department item) throws SQLException {
		String sql = "insert into department values(?, ?, ?)";
	/*	connection = ConnectionFactory.getConnection();*/
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNO());
		pstmt.setString(2, item.getDeptName());
		pstmt.setInt(3, item.getFloor());
		pstmt.executeUpdate();
		DBUtil.close(pstmt/*, connection*/);
		
/*		pstmt.close();	
		connection.close();*/
/*		System.out.println(pstmt);*/
		
/*		int res = pstmt.executeUpdate(); //executeQuery(select문), executeUpdate 구별해서 사용할것..
		System.out.println(res);*/
	}
;
	@Override
	public void deleteItembyNo(Department item) throws SQLException {
		String sql = "delete from department where deptno = ?";
	/*	connection = ConnectionFactory.getConnection();*/
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getDeptNO());
/*		System.out.println(pstmt);*/
		pstmt.executeUpdate();
		DBUtil.close(pstmt/*, connection*/);
	}

	@Override
	public void updateItembyNo(Department item) throws SQLException {
		String sql = "update department set deptname= ?, floor = ? where deptno = ?	";
		/*connection = ConnectionFactory.getConnection();*/
		pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getDeptName());
		pstmt.setInt(2, item.getFloor());
		pstmt.setInt(3, item.getDeptNO());
/*		System.out.println(pstmt);*/
		pstmt.executeUpdate();
		DBUtil.close(pstmt/*, connection*/);
	}

	@Override
	public ArrayList<Department> listItems() throws SQLException {
		ArrayList<Department> list = new ArrayList<>();
		String sql = "Select deptno, deptname, floor from department";
		/*connection = ConnectionFactory.getConnection();*/
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			list.add(new Department(rs.getInt("deptno"), rs.getString("deptname"), rs.getInt("floor")));
		}
		DBUtil.close(rs/*, connection*/); //역순으로 종료
		DBUtil.close(pstmt);
		
		return list;
	}


	@Override
	public Department searchItem(Department item) throws SQLException {
		Department department = new Department();
		String sql = "select deptno, deptname, floor from department where deptno = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1,item.getDeptNO());
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			department.setDeptNO(rs.getInt(1));
			department.setDeptName(rs.getString(2));
			department.setFloor(rs.getInt(3));
		}
			
		return department;
	}
}
